package lzit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import lzit.entity.Shanghus;
import lzit.util.DBUtil;

public class ShanghusDao {
    public int add(Shanghus m) {
        int row = 0;
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DBUtil.getCon();
            String sql = "insert into Shanghus(sh_name,sh_quyu,sh_lianxiren,sh_dianhua) values(?,?,?,?)";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, m.getSh_name());
            stmt.setString(2, m.getSh_quyu());
            stmt.setString(3, m.getSh_lianxiren());
            stmt.setString(4, m.getSh_dianhua());
            row = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.release(null, stmt, con);
        }
        return row;
    }

    public Shanghus findShById(int id) {
        int row = 0;
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getCon();
            String sql = "select * from SHANGHUS where id=?";
            stmt = con.prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            if (rs.next()) {
                Shanghus shanghu = new Shanghus();
                shanghu.setId(rs.getInt("id"));
                shanghu.setSh_name(rs.getString("sh_name"));
                shanghu.setSh_quyu(rs.getString("sh_quyu"));
                shanghu.setSh_lianxiren(rs.getString("sh_lianxiren"));
                shanghu.setSh_dianhua(rs.getString("sh_dianhua"));
                return shanghu;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.release(rs, stmt, con);
        }
        return null;
    }

    public int update(Shanghus m) {
        int row = 0;
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DBUtil.getCon();
            String sql = "update Shanghus set sh_name=?,sh_quyu=?,sh_lianxiren=?,sh_dianhua=? where id=?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, m.getSh_name());
            stmt.setString(2, m.getSh_quyu());
            stmt.setString(3, m.getSh_lianxiren());
            stmt.setString(4, m.getSh_dianhua());
            stmt.setInt(5, m.getId());
            row = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.release(null, stmt, con);
        }
        return row;
    }

//    public int delete(Shanghus m) {
//        int row = 0;
//        Connection con = null;
//        PreparedStatement stmt = null;
//        try {
//            con = DBUtil.getCon();
//            String sql = "delete from Shanghus where id=?";
//            stmt = con.prepareStatement(sql);
//            stmt.setInt(1, m.getId());
//            row = stmt.executeUpdate();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            DBUtil.release(null, stmt, con);
//        }
//        return row;
//    }

    public int delete(int id) {
        int row = 0;
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DBUtil.getCon();
            String sql = "delete from Shanghus where id=?";
            stmt = con.prepareStatement(sql);
            stmt.setInt(1, id);
            row = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.release(null, stmt, con);
        }
        return row;
    }

    public List<Shanghus> findAllSh(String sql) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getCon();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            List<Shanghus> list = new ArrayList<Shanghus>();
            while (rs.next()) {
                Shanghus shanghus = new Shanghus();
                shanghus.setId(rs.getInt("id"));
                shanghus.setSh_name(rs.getString("sh_name"));
                shanghus.setSh_quyu(rs.getString("sh_quyu"));
                shanghus.setSh_lianxiren(rs.getString("sh_lianxiren"));
                shanghus.setSh_dianhua(rs.getString("sh_dianhua"));
                list.add( shanghus);
            }
            return list;
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } finally {
            DBUtil.release(rs, stmt, con);
        }
        return null;
    }
}
